package com.ctshk.rpc.order.custom.mapper;


import java.util.List;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.annotations.Update;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.ctshk.common.vo.MainDataCount;
import com.ctshk.rpc.order.custom.base.CustomBaseMapper;
import com.ctshk.rpc.order.custom.dto.order.CustomOrderPageTouristPeopleDTO;
import com.ctshk.rpc.order.custom.dto.order.CustomOrderTouristCollectionDTO;
import com.ctshk.rpc.order.custom.dto.tourist.CustomGroupTouristInfoDTO;
import com.ctshk.rpc.order.custom.dto.tourist.CustomOrderTouristInfoDTO;
import com.ctshk.rpc.order.custom.dto.tourist.CustomOrderTouristInfoListCollectionDTO;
import com.ctshk.rpc.order.custom.dto.tourist.CustomOrderTouristNameListDTO;
import com.ctshk.rpc.order.custom.entity.CustomizedOrderTouristInfo;
import com.ctshk.rpc.order.custom.provider.CustomOrderTouristInfoProvider;
import com.ctshk.rpc.order.custom.req.order.CustomOrderIdReq;
import com.ctshk.rpc.order.custom.req.tourist.CustomOrderTouristNameListReq;

/**
 * <p>
 * 游客信息 Mapper 接口
 * </p>
 *
 * @author 叶冠峰
 * @since 2021-02-22
 */
public interface CustomizedOrderTouristInfoMapper extends CustomBaseMapper<CustomizedOrderTouristInfo> {

    /**
     * 收客情况
     * @param orderId
     * @return
     */
    @Select("SELECT tourist_type_name,COUNT(*) AS people \n" +
            "FROM t_customized_order_tourist_info\n" +
            "WHERE order_id = #{order_id} GROUP BY main_data_id")
    List<CustomOrderPageTouristPeopleDTO> queryTouristByType(@Param("order_id") Long orderId);

    /**
     * 旅客信息列表
     * @param page
     * @param req
     * @return
     */
    @SelectProvider(type = CustomOrderTouristInfoProvider.class, method = "selectListBySelective")
    IPage<CustomOrderTouristInfoDTO> selectListBySelective(IPage<CustomOrderIdReq> page, CustomOrderIdReq req);

    /**
     *
     * @param orderId
     * @param emergencyContactName
     * @param emergencyMobileAreaCode
     * @param emergencyMobile
     * @return
     */
    @Update("UPDATE t_customized_order_tourist_info SET\n" +
            "emergency_contact_name = #{emergency_contact_name},emergency_mobile_area_code = #{emergency_mobile_area_code}, emergency_mobile = #{emergency_mobile}\n" +
            "WHERE order_id = #{order_id} AND is_deleted = 0")
    Integer applyEmergencyAll(@Param("order_id") Long orderId, @Param("emergency_contact_name") String emergencyContactName, @Param("emergency_mobile_area_code") String emergencyMobileAreaCode, @Param("emergency_mobile") String emergencyMobile);

    /**
     * 收款列表
     * @param pageParam
     * @param req
     * @return
     */
    @SelectProvider(type = CustomOrderTouristInfoProvider.class, method = "selectListCollectionBySelective")
    IPage<CustomOrderTouristInfoListCollectionDTO> selectListCollectionBySelective(@Param("page") IPage<CustomOrderIdReq> pageParam, @Param("req") CustomOrderIdReq req);

    /**
     * 团期详情 - 收款明细
     * @param scheduleId
     * @return
     */
    @SelectProvider(type = CustomOrderTouristInfoProvider.class, method = "reciveListBySchedule")
    List<CustomOrderTouristInfoListCollectionDTO> reciveListBySchedule(@Param("schedule_id")Long scheduleId);

    /**
     * 收款明细
     * @param id
     * @return
     */
    @SelectProvider(type = CustomOrderTouristInfoProvider.class, method = "reciveListByTourist")
    List<CustomOrderTouristCollectionDTO> reciveListByTourist(@Param("id") Long id);

    /**
     * 根据旅客类型统计数量
     * @param orderId
     * @return
     */
    @Select("SELECT main_data_id,COUNT(id) AS countNumber \n" +
            "FROM t_customized_order_tourist_info \n" +
            "WHERE order_id = #{order_id} AND is_deleted = 0\n" +
            "GROUP BY main_data_id")
    List<MainDataCount> queryCountByMainDataId(@Param("order_id") Long orderId);

    @Select("<script>"+"SELECT t.id id,t.order_id, t.lock_status lockStatus,t.noShow noShow,t.en_fullname enFullname,t.cn_fullname cnFullname,\n" +
            "t.people_type peopleType ,t.tourist_type_name touristTypeName,t.pay_status payStatus,t.birthday birthday,t.gender gender,t.nation nation,t.mobile mobile,t.email email\n" +
            ",o.order_number orderNumber,o.order_source orderSource,t.create_id createId,\n" +
            "t.certificate_type certificateType,t.certificate_number certificateNumber,t.certificate_valid_date\n" +
            "certificateValidDate,\n" +
            "t.certificate_type2 certificateType2,t.certificate_number2 certificateNumber2,t.certificate_valid_date2 certificateValidDate2,\n" +
            "t.certificate_type3 certificateType3,t.certificate_number3 certificateNumber3,t.certificate_valid_date3 certificateValidDate3, o.internal_remark internalRemark,\n" +
            "t.buy_visa buyVisa,v.collection_status collectionStatus,v.submit_time submitTime,v.submit_tracking_number submitTrackingNumber,v.make_up_time makeUpTime,v.make_up_tracking_number makeUpTrackingNumber, v.sign_out_status signOutStatus\n" +
            " FROM t_customized_order_tourist_info t left join t_customized_order o ON t.order_id = o.id left join t_customized_order_tourist_visa v on t.id = v.Tourist_id where t.is_deleted = 0 and o.is_deleted = 0  and t.schedule_id = #{req.scheduleId}\n" +
            "<if test=\"req.orderNumber != null and req.orderNumber != ''\">AND o.order_number LIKE CONCAT('%',#{req.orderNumber},'%')</if>\n" +
            "<if test=\"req.name != null and req.name != ''\">and (t.cn_fullname LIKE CONCAT('%',#{req.name},'%') or t.en_fullname LIKE CONCAT('%',#{req.name},'%'))</if>\n" +
            "<if test=\"req.mobile != null and req.mobile != ''\">and t.mobile LIKE CONCAT('%',#{req.mobile},'%')</if>\n" +
            "<if test=\"req.touristTypeName != null and req.touristTypeName != ''\">AND t.tourist_type_name LIKE CONCAT('%',#{req.touristTypeName},'%')</if>\n" +
            "<if test=\"req.gender != null\">AND t.gender = #{req.gender}</if> \n" +
            "<if test=\"req.lockStatus != null\">AND t.lock_status = #{req.lockStatus}</if> \n" +
            "<if test=\"req.noShow != null\">AND t.noShow IS NOT NULL AND t.noShow = #{req.noShow}</if> \n" +
            "<if test=\"req.payStatus != null\">AND t.pay_status = #{req.payStatus}</if> \n" +
            "<if test=\"req.orderSource != null\">AND o.order_source = #{req.orderSource}</if> \n" +
            "<if test=\"req.buyVisa != null\">AND t.buy_visa = #{req.buyVisa}</if> \n" +
            "<if test=\"req.collectionStatus != null\">AND v.collection_status = #{req.collectionStatus}</if> \n" +
            "<if test=\"req.signOutStatus != null\">AND v.sign_out_status = #{req.signOutStatus}</if> \n" +
            "ORDER BY t.gmt_create DESC"+"</script>")
    IPage<CustomOrderTouristNameListDTO> queryTouristList(IPage<CustomOrderTouristNameListDTO> page,@Param("req") CustomOrderTouristNameListReq req);


    @Select("<script>"+"select t.main_data_id peopleType,count(1) peopleNum from t_customized_order_tourist_info t WHERE t.schedule_id = #{sid} and t.is_deleted = 0  group by t.main_data_id"+"</script>")
    List<CustomGroupTouristInfoDTO> groupEnrollNum(@Param("sid")Long id);

    @Select("<script>"+"select t.main_data_id peopleType, sum(sd.total_people) peopleNum from t_customized_order_tourist_info t right join t_customized_order sd on t.order_id = sd.id where t.is_deleted = 0 and sd.is_deleted = 0 and sd.plus_tourist_type = 2 and t.schedule_id = #{sid}  group by t.main_data_id"+"</script>")
    List<CustomGroupTouristInfoDTO> groupSurplusNum(@Param("sid")Long id);

    /**
     * 根据团期ID获取旅客名单
     * @param scheduleId
     * @return
     */
    @Select("SELECT t.id id,t.order_id, t.lock_status lockStatus,t.noShow noShow,t.en_fullname enFullname,t.cn_fullname cnFullname,t.emergency_contact_name,t.emergency_mobile_area_code,t.emergency_mobile,\n" +
            "t.people_type peopleType ,t.tourist_type_name touristTypeName,t.pay_status payStatus,t.birthday birthday,t.gender gender,t.nation nation,t.mobile mobile,t.email email\n" +
            ",o.order_number orderNumber,o.order_source orderSource,t.create_id createId,\n" +
            "t.certificate_type certificateType,t.certificate_number certificateNumber,t.certificate_valid_date\n" +
            "certificateValidDate,\n" +
            "t.certificate_type2 certificateType2,t.certificate_number2 certificateNumber2,t.certificate_valid_date2 certificateValidDate2,\n" +
            "t.certificate_type3 certificateType3,t.certificate_number3 certificateNumber3,t.certificate_valid_date3 certificateValidDate3, o.internal_remark internalRemark,\n" +
            "t.buy_visa buyVisa,v.collection_status collectionStatus,v.submit_time submitTime,v.submit_tracking_number submitTrackingNumber,v.make_up_time makeUpTime,v.make_up_tracking_number makeUpTrackingNumber, v.sign_out_status signOutStatus\n" +
            " FROM t_customized_order_tourist_info t left join t_customized_order o ON t.order_id = o.id left join t_customized_order_tourist_visa v on t.id = v.Tourist_id where t.is_deleted = 0 and o.is_deleted = 0  and t.schedule_id = #{schedule_id}\n" +
            "ORDER BY t.gmt_create DESC")
    List<CustomOrderTouristNameListDTO> queryListBySchedule(@Param("schedule_id")Long scheduleId);

    /**
     * 行程报价统计订单人数
     * @param id
     * @return
     */
    @Select("SELECT main_data_id,tourist_type_name,COUNT(*) AS people\n" +
            "FROM t_customized_order_tourist_info\n" +
            "WHERE order_id = #{order_id} AND is_deleted = 0\n" +
            "GROUP BY main_data_id")
    List<CustomOrderPageTouristPeopleDTO> countByTouristType(@Param("order_id") Long id);

    /**
     * 重置团期旅客groupId
     * @param scheduleId
     * @param userId
     */
    @Update("UPDATE t_customized_order_tourist_info\n" +
            "SET group_id = NULL,modified_id = #{user_id}\n" +
            "WHERE schedule_id = #{schedule_id} AND is_deleted = 0")
    void resetGroupByIdList(@Param("schedule_id")Long scheduleId,@Param("user_id") Long userId);
}
